# !/use/bin/env python
# -*- coding:utf-8 -*-

import os

import openpyxl


class OperatorExcel:

    def __init__(self, url):
        self.file_url = url.replace("test_case", "test_case_excel").replace(".py", ".xlsx")
        print("测试用例路径为：" + self.file_url)

    """
    @file_name  目标Excel路径
    @sheet_name  对应的sheet页
    @:return     二维数组；Excel表中所有数据【不包括第一行，标题行】
    @describe:读取Excel表格中的内容
    @staticmethod
    """

    @classmethod
    def read_all_excel(cls, file_name, sheet_name):
        excel_content = []

        wb = openpyxl.load_workbook(file_name, False)
        sheet = wb[sheet_name]

        for row in sheet.rows:
            row_content = []
            for cell in row:
                row_content.append(cell.value)
            excel_content.append(row_content)

        del excel_content[0]
        return excel_content

    """
     @file_name  目标Excel路径
     @sheet_name  
     @row         int  ,要读取的行
     @:return     数组  Excel表格中某一行数据
     @describe:读取Excel表格中某一行的数据
     """

    @staticmethod
    def read_certain_row(file_name, sheet_name, row):
        wb = openpyxl.load_workbook(file_name, read_only=False)
        sheet = wb[sheet_name]

        row_content = []
        for cell in sheet[row]:
            row_content.append(cell.value)

        return row_content

    """
    @file_name
    @sheet_name
    @title      列名
    @:return    int  title在Excel表中的第几列
    @describe   从Excel中找到名为title列的index并返回
    """

    @staticmethod
    def get_col_by_title(file_name, sheet_name, title):
        wb = openpyxl.load_workbook(file_name, read_only=False)
        sheet = wb[sheet_name]
        col_index = 0
        for cell in sheet[1]:
            col_index = col_index + 1
            if cell.value == title:
                return col_index

    @staticmethod
    def get_excel_row(file_name, sheet_name):
        wb = openpyxl.load_workbook(file_name, read_only=False)
        sheet = wb[sheet_name]
        return sheet.max_row

    """
    @file_name  目标Excel路径
    @sheet_name  
    @content     数组；[{'row':'','col':'','text':''}...]
    @describe    将数据写入到Excel指定的(row, col)中
    """

    @staticmethod
    def write_excel(file_name, sheet_name, content):
        wb = openpyxl.load_workbook(file_name, read_only=False)
        sheet = wb[sheet_name]
        for i in range(0, len(content)):
            item = content[i]
            sheet.cell(int(item['row']), int(item['col']), value=item['text'])
        wb.save(file_name)

    @classmethod
    def test(cls):
        a = os.path.abspath(__file__)
        return a
